using Oracle.ManagedDataAccess.Client;
using System.Data;
using Microsoft.Extensions.Configuration;
using System.Collections;

namespace Lzfy_His_Service.Services
{
    /// <summary>
    /// 基于DbHelperHisBi最佳实践的Oracle连接管理器
    /// 提供严格的连接生命周期管理和连接池优化
    /// </summary>
    public class OptimizedOracleConnectionManager : IOracleConnectionManager
    {
        private readonly string _connectionString;
        private readonly ILogger<OptimizedOracleConnectionManager> _logger;

        public OptimizedOracleConnectionManager(
            IConfiguration configuration,
            ILogger<OptimizedOracleConnectionManager> logger)
        {
            _connectionString = configuration.GetConnectionString("HisOracleConnection") ?? string.Empty;
            _logger = logger;
            
            if (string.IsNullOrEmpty(_connectionString))
            {
                _logger.LogError("Oracle连接字符串未配置: HisOracleConnection");
            }
        }

        #region 基础查询方法 - 遵循DbHelperHisBi模式

        /// <summary>
        /// 执行SQL语句，返回影响的记录数 - 严格using模式
        /// </summary>
        public async Task<int> ExecuteSqlAsync(string sql, params OracleParameter[] parameters)
        {
            if (string.IsNullOrEmpty(_connectionString))
            {
                throw new InvalidOperationException("Oracle连接字符串未配置");
            }

            using (var connection = new OracleConnection(_connectionString))
            {
                using (var cmd = new OracleCommand(sql, connection))
                {
                    try
                    {
                        await connection.OpenAsync();
                        if (parameters != null && parameters.Length > 0)
                        {
                            cmd.Parameters.AddRange(parameters);
                        }
                        return await cmd.ExecuteNonQueryAsync();
                    }
                    catch (OracleException ex)
                    {
                        _logger.LogError(ex, "执行SQL失败: {Sql}", sql);
                        throw new Exception($"执行SQL失败: {ex.Message}", ex);
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataTable - 完全using模式
        /// </summary>
        public async Task<DataTable> GetDataTableAsync(string sql, params OracleParameter[] parameters)
        {
            if (string.IsNullOrEmpty(_connectionString))
            {
                throw new InvalidOperationException("Oracle连接字符串未配置");
            }

            using (var connection = new OracleConnection(_connectionString))
            {
                using (var cmd = new OracleCommand(sql, connection))
                {
                    if (parameters != null && parameters.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    
                    using (var adapter = new OracleDataAdapter(cmd))
                    {
                        try
                        {
                            await connection.OpenAsync();
                            var dataTable = new DataTable();
                            adapter.Fill(dataTable);
                            return dataTable;
                        }
                        catch (OracleException ex)
                        {
                            _logger.LogError(ex, "查询数据失败: {Sql}", sql);
                            throw new Exception($"查询数据失败: {ex.Message}", ex);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回单个值
        /// </summary>
        public async Task<object?> GetSingleAsync(string sql, params OracleParameter[] parameters)
        {
            if (string.IsNullOrEmpty(_connectionString))
            {
                throw new InvalidOperationException("Oracle连接字符串未配置");
            }

            using (var connection = new OracleConnection(_connectionString))
            {
                using (var cmd = new OracleCommand(sql, connection))
                {
                    if (parameters != null && parameters.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }

                    try
                    {
                        await connection.OpenAsync();
                        var result = await cmd.ExecuteScalarAsync();
                        return (result == null || result == DBNull.Value) ? null : result;
                    }
                    catch (OracleException ex)
                    {
                        _logger.LogError(ex, "获取单个值失败: {Sql}", sql);
                        throw new Exception($"获取单个值失败: {ex.Message}", ex);
                    }
                }
            }
        }

        /// <summary>
        /// 执行事务操作 - 完整的事务管理
        /// </summary>
        public async Task ExecuteSqlTransactionAsync(List<string> sqlList, List<OracleParameter[]>? parameterList = null)
        {
            if (string.IsNullOrEmpty(_connectionString))
            {
                throw new InvalidOperationException("Oracle连接字符串未配置");
            }

            if (sqlList == null || sqlList.Count == 0)
            {
                throw new ArgumentException("SQL列表不能为空", nameof(sqlList));
            }

            using (var connection = new OracleConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var transaction = connection.BeginTransaction())
                {
                    using (var cmd = new OracleCommand { Connection = connection, Transaction = transaction })
                    {
                        try
                        {
                            for (int i = 0; i < sqlList.Count; i++)
                            {
                                cmd.CommandText = sqlList[i];
                                cmd.Parameters.Clear();

                                if (parameterList != null && parameterList.Count > i && parameterList[i] != null)
                                {
                                    cmd.Parameters.AddRange(parameterList[i]);
                                }

                                await cmd.ExecuteNonQueryAsync();
                            }

                            transaction.Commit();
                            _logger.LogInformation("事务执行成功，共执行 {Count} 条SQL", sqlList.Count);
                        }
                        catch (OracleException ex)
                        {
                            transaction.Rollback();
                            _logger.LogError(ex, "事务执行失败，已回滚");
                            throw new Exception($"事务执行失败: {ex.Message}", ex);
                        }
                    }
                }
            }
        }

        #endregion

        #region 高级查询方法 - 带连接池监控

        /// <summary>
        /// 执行查询并返回OracleDataReader - 带连接池监控
        /// </summary>
        public async Task<OracleDataReader> ExecuteReaderAsync(string sql, params OracleParameter[] parameters)
        {
            if (string.IsNullOrEmpty(_connectionString))
            {
                throw new InvalidOperationException("Oracle连接字符串未配置");
            }

            var connection = new OracleConnection(_connectionString);
            var cmd = new OracleCommand(sql, connection);

            try
            {
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                await connection.OpenAsync();
                return await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
            }
            catch (OracleException ex)
            {
                connection?.Dispose();
                cmd?.Dispose();
                _logger.LogError(ex, "执行Reader失败: {Sql}", sql);
                throw new Exception($"执行Reader失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取连接池状态信息
        /// </summary>
        public Dictionary<string, object> GetConnectionPoolStats()
        {
            return new Dictionary<string, object>
            {
                ["ConnectionStringHash"] = _connectionString.GetHashCode(),
                ["CurrentTime"] = DateTime.Now,
                ["ConnectionTimeout"] = 60,
                ["MaxPoolSize"] = 100,
                ["MinPoolSize"] = 10,
                ["PoolingEnabled"] = true,
                ["ValidateConnection"] = true
            };
        }

        #endregion

        #region 参数辅助方法

        /// <summary>
        /// 创建Oracle参数 - 简化参数创建
        /// </summary>
        public OracleParameter CreateParameter(string name, OracleDbType type, object? value)
        {
            if (string.IsNullOrEmpty(name))
            {
                throw new ArgumentException("参数名不能为空", nameof(name));
            }
            return new OracleParameter(name, type) { Value = value ?? DBNull.Value };
        }

        /// <summary>
        /// 创建输入参数
        /// </summary>
        public OracleParameter CreateInputParameter(string name, OracleDbType type, object? value)
        {
            if (string.IsNullOrEmpty(name))
            {
                throw new ArgumentException("参数名不能为空", nameof(name));
            }
            return new OracleParameter(name, type) { Value = value ?? DBNull.Value, Direction = ParameterDirection.Input };
        }

        /// <summary>
        /// 创建输出参数
        /// </summary>
        public OracleParameter CreateOutputParameter(string name, OracleDbType type)
        {
            if (string.IsNullOrEmpty(name))
            {
                throw new ArgumentException("参数名不能为空", nameof(name));
            }
            return new OracleParameter(name, type) { Direction = ParameterDirection.Output };
        }

        #endregion
    }

    /// <summary>
    /// 连接管理接口
    /// </summary>
    public interface IOracleConnectionManager
    {
        Task<int> ExecuteSqlAsync(string sql, params OracleParameter[] parameters);
        Task<DataTable> GetDataTableAsync(string sql, params OracleParameter[] parameters);
        Task<object?> GetSingleAsync(string sql, params OracleParameter[] parameters);
        Task ExecuteSqlTransactionAsync(List<string> sqlList, List<OracleParameter[]>? parameterList = null);
        Task<OracleDataReader> ExecuteReaderAsync(string sql, params OracleParameter[] parameters);
        Dictionary<string, object> GetConnectionPoolStats();
    }
}